﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Data.SqlClient;
using System.Data;

namespace FrmHotel
{
    class DBHelper
    {
        //1.定义链接字符串
        public const string CONNSTR = "server=.;dataBase=Hotel;uid=sa;pwd=sa";
        private static SqlConnection _conn;
        public static SqlConnection Conn
        {
            get
            {
                if (_conn == null)
                {
                    _conn = new SqlConnection(CONNSTR);
                }
                if (_conn.State == ConnectionState.Broken)
                {
                    _conn.Close();
                }
                if (_conn.State == ConnectionState.Closed)
                {
                    _conn.Open();
                }
                return _conn;
            }
        }
        /// <summary>
        /// 查询单个信息
        /// </summary>
        /// <param name="sql">要执行的sql语句</param>
        /// <returns>查询到的结果</returns>
        public static object SelectOne(string sql)
        {
            object o = null;
            SqlCommand comm = null;
            try
            {
                comm = new SqlCommand(sql, Conn);
                o = comm.ExecuteScalar();
            }
            catch (Exception e)
            {
                Console.WriteLine("数据库链接异常：" + e);
            }
            finally
            {
                comm.Connection.Close();
            }
            return o;
        }
        /// <summary>
        /// 执行增删改
        /// </summary>
        /// <param name="sql"></param>
        /// <returns></returns>
        public static int ExecuteUpdate(string sql)
        {
            int result = -1;
            SqlCommand comm = null;
            try
            {
                comm = new SqlCommand(sql, Conn);
                result = comm.ExecuteNonQuery();
            }
            catch (Exception e)
            {
                Console.WriteLine("数据库链接异常：" + e);
            }
            finally
            {
                comm.Connection.Close();
            }
            return result;
        }
        /// <summary>
        ///  查询多个信息
        /// </summary>
        /// <param name="sql">要执行的sql语句</param>
        /// <returns>结果集</returns>
        public static SqlDataReader SelectMore(string sql)
        {
            SqlDataReader reader = null;
            try
            {
                SqlCommand comm = new SqlCommand(sql, Conn);
                return reader = comm.ExecuteReader(CommandBehavior.CloseConnection);
               
            }
            catch (Exception e)
            {
                Console.WriteLine("数据库链接异常：" + e);
            }
            return reader;
        }
        /// <summary>
        /// 查询数据到表格中
        /// </summary>
        /// <param name="sql">要执行的查询语句</param>
        /// <returns>查询到的表结果</returns>
        public static DataTable SelectForTable(string sql)
        {
            DataTable dt = new DataTable();
            try
            {
                SqlDataAdapter da = new SqlDataAdapter(sql, Conn);
                da.Fill(dt);
            }
            catch (Exception e)
            {
                System.Windows.Forms.MessageBox.Show("数据库链接异常" + e);
            }
            return dt;
        }
    }

}
